
clear
cd "C:\Users\brdblank\Dropbox\AGCS Dataset\Harvard Dataverse"
cd "C:\Users\Brian\Dropbox\AGCS Dataset\Harvard Dataverse"
import delimited "Contracts_Africa.csv"


foreach var of varlist access-warehouse {
replace `var' = "0" if `var'=="FALSE"
replace `var' = "1" if `var'=="TRUE"
destring `var', replace
}

egen total = rowtotal(access-warehouse)
gen school_hospital = 0
replace school_hospital = 1 if school=="TRUE"
replace school_hospital = 1 if hospital=="TRUE"
replace school_hospital = 1 if clinic=="TRUE"
sort total
order total req_desc

keep total req_desc pop_country sign_year psc_code school_hospital

gen psc_flag = 0
replace psc_flag = 1 if psc_code=="V112"
replace psc_flag = 1 if psc_code=="W023"
replace psc_flag = 1 if psc_code=="W035"
replace psc_flag = 1 if psc_code=="W038"
replace psc_flag = 1 if psc_code=="W085"
replace psc_flag = 1 if psc_code=="W099"
replace psc_flag = 1 if psc_code=="X1FC"
replace psc_flag = 1 if psc_code=="X1JZ"
replace psc_flag = 1 if psc_code=="Y1EB"
replace psc_flag = 1 if psc_code=="Y199"
replace psc_flag = 1 if psc_code=="Y211"
replace psc_flag = 1 if psc_code=="Y1AZ"
replace psc_flag = 1 if psc_code=="Y1EA"
replace psc_flag = 1 if psc_code=="Y1EZ"
replace psc_flag = 1 if psc_code=="Y1JZ"
replace psc_flag = 1 if psc_code=="Y1KA"
replace psc_flag = 1 if psc_code=="Y1LZ"
replace psc_flag = 1 if psc_code=="Y1NE"
replace psc_flag = 1 if psc_code=="Y1QA"
replace psc_flag = 1 if psc_code=="Z1PZ"
replace psc_flag = 1 if psc_code=="Z2PZ"
replace psc_flag = 1 if psc_code=="Z2JZ"
replace psc_flag = 1 if psc_code=="Z2NE"
replace psc_flag = 1 if psc_code=="Z2BD"
replace psc_flag = 1 if psc_code=="Z2FC"
replace psc_flag = 1 if psc_code=="8960"

replace psc_flag = 1 if psc_code=="C219"
replace psc_flag = 1 if psc_code=="N062"
replace psc_flag = 1 if psc_code=="R499"
replace psc_flag = 1 if psc_code=="R706"
replace psc_flag = 1 if psc_code=="R799"

replace psc_flag = 1 if psc_code=="L015" 

replace psc_flag = 1 if psc_code=="S203"

replace psc_flag = 1 if psc_code=="S209"

replace psc_flag = 1 if psc_code=="S216"

replace psc_flag = 1 if psc_code=="S299"


gen psc_type = substr(psc_code,1,1)
gen psc_topcode = substr(psc_code,1,2)
gen base_contract = 1

preserve
keep if psc_type=="E" | psc_type=="L" | psc_type=="V" | psc_type=="W" | psc_type=="X" | psc_type=="Y" | psc_type=="Z" | psc_code=="8960"
drop if total==0
collapse (sum) base_contracts_keyword=base_contract, by(pop sign)
rename pop country
rename sign year
replace country = "Yemen, Rep." if country=="Yemen"
replace country = "Venezuela, RB" if country=="Venezuela"
replace country = "Trinidad and Tobago" if country=="Trinidad And Tobago"
replace country = "Syrian Arab Republic" if country=="Syria"
replace country = "Slovak Republic" if country=="Slovakia"
replace country = "Russian Federation" if country=="Russia"
replace country = "Sao Tome and Principe" if country=="Sao Tome And Principe"
replace country = "Macedonia, FYR" if country=="Macedonia"
replace country = "Kyrgyz Republic" if country=="Kyrgyzstan"
replace country = "Lao PDR" if country=="Laos"
replace country = "Korea, Rep." if country=="Korea, South"
replace country = "Korea, Dem. Rep." if country=="Korea, North"
replace country = "Iran, Islamic Rep." if country=="Iran"
replace country = "Egypt, Arab Rep." if country=="Egypt"
replace country = "Cote d'Ivoire" if country=="Cote D'Ivoire"
replace country = "Congo, Rep." if country=="Congo (Brazzaville)"
replace country = "Congo, Dem. Rep." if country=="Congo (Kinshasa)"
replace country = "Cabo Verde" if country=="Cape Verde"
replace country = "Myanmar" if country=="Burma"
replace country = "Bosnia and Herzegovina" if country=="Bosnia And Herzegovina"
replace country = "Brunei Darussalam" if country=="Brunei"
replace country = "Antigua and Barbuda" if country=="Antigua And Barbuda"

egen id = group(country)
xtset id year
tsfill, full
gen criteria1 = 0
replace criteria1 = 1 if base_contracts_keyword > 0 & F1.base_contracts_keyword > 0 & F2.base_contracts_keyword > 0 & base_contracts_keyword != 0 & F1.base_contracts_keyword != . & F2.base_contracts_keyword != .
replace criteria1 = 1 if base_contracts_keyword > 0 & F1.base_contracts_keyword > 0 & base_contracts_keyword != 0 & F1.base_contracts_keyword != .
replace criteria1 = 1 if base_contracts_keyword > 0 & L1.base_contracts_keyword > 0 & base_contracts_keyword != 0 & L1.base_contracts_keyword != .
replace criteria1 = 1 if base_contracts_keyword > 0 & L1.base_contracts_keyword > 0 & L2.base_contracts_keyword > 0 & base_contracts_keyword != 0 & L1.base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria1 = 1 if L1.base_contracts_keyword > 0 & L2.base_contracts_keyword > 0 & L1.base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria1 = 1 if base_contracts_keyword > 0 & L2.base_contracts_keyword > 0 & base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria1 = 1 if L1.base_contracts_keyword > 0 & F1.base_contracts_keyword > 0 & L1.base_contracts_keyword != 0 & F1.base_contracts_keyword != .
replace criteria1 = 1 if base_contracts_keyword > 0 & F2.base_contracts_keyword > 0 & base_contracts_keyword != 0 & F2.base_contracts_keyword != .

gen criteria2 = 0
replace criteria2 = 1 if ((base_contracts_keyword+F1.base_contracts_keyword+F2.base_contracts_keyword) > 4) & base_contracts_keyword != . & base_contracts_keyword != 0 & F1.base_contracts_keyword != . & F2.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+L1.base_contracts_keyword+L2.base_contracts_keyword) > 4) & base_contracts_keyword != . & base_contracts_keyword != 0 & L1.base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+L1.base_contracts_keyword+F1.base_contracts_keyword) > 4) & base_contracts_keyword != . & base_contracts_keyword != 0 & L1.base_contracts_keyword != . & F1.base_contracts_keyword != .
replace criteria2 = 1 if ((L1.base_contracts_keyword+L2.base_contracts_keyword) > 4) & base_contracts_keyword != . & L1.base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+F1.base_contracts_keyword) > 4) & base_contracts_keyword != . & F1.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+F2.base_contracts_keyword) > 4) & base_contracts_keyword != . & F2.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+L1.base_contracts_keyword) > 4) & base_contracts_keyword != . & L1.base_contracts_keyword != .
replace criteria2 = 1 if ((base_contracts_keyword+L2.base_contracts_keyword) > 4) & base_contracts_keyword != . & L2.base_contracts_keyword != .
replace criteria2 = 1 if ((F1.base_contracts_keyword+L1.base_contracts_keyword) > 4) & base_contracts_keyword != .  & L1.base_contracts_keyword != . & F1.base_contracts_keyword != .

gen base = 0
replace base = 1 if criteria1==1 & criteria2==1
label var base "US military facility (Africa)"
restore




preserve
keep if psc_type=="E" | psc_type=="L" | psc_type=="V" | psc_type=="W" | psc_type=="X" | psc_type=="Y" | psc_type=="Z" | psc_code=="8960"
*keep if psc_type=="V" | psc_type=="W" | psc_type=="X" | psc_type=="Y" | psc_type=="Z"
drop if total==0
drop if school_hospital==1
collapse (sum) base_contracts_keyword=base_contract, by(pop sign)
rename pop country
rename sign year
replace country = "Yemen, Rep." if country=="Yemen"
replace country = "Venezuela, RB" if country=="Venezuela"
replace country = "Trinidad and Tobago" if country=="Trinidad And Tobago"
replace country = "Syrian Arab Republic" if country=="Syria"
replace country = "Slovak Republic" if country=="Slovakia"
replace country = "Russian Federation" if country=="Russia"
replace country = "Sao Tome and Principe" if country=="Sao Tome And Principe"
replace country = "Macedonia, FYR" if country=="Macedonia"
replace country = "Kyrgyz Republic" if country=="Kyrgyzstan"
replace country = "Lao PDR" if country=="Laos"
replace country = "Korea, Rep." if country=="Korea, South"
replace country = "Korea, Dem. Rep." if country=="Korea, North"
replace country = "Iran, Islamic Rep." if country=="Iran"
replace country = "Egypt, Arab Rep." if country=="Egypt"
replace country = "Cote d'Ivoire" if country=="Cote D'Ivoire"
replace country = "Congo, Rep." if country=="Congo (Brazzaville)"
replace country = "Congo, Dem. Rep." if country=="Congo (Kinshasa)"
replace country = "Cabo Verde" if country=="Cape Verde"
replace country = "Myanmar" if country=="Burma"
replace country = "Bosnia and Herzegovina" if country=="Bosnia And Herzegovina"
replace country = "Brunei Darussalam" if country=="Brunei"
replace country = "Antigua and Barbuda" if country=="Antigua And Barbuda"
rename base_contracts_keyword base_contracts_keyword_school

egen id = group(country)
xtset id year
tsfill, full
gen criteria1_school = 0
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & F1.base_contracts_keyword_school > 0 & F2.base_contracts_keyword_school > 0 & base_contracts_keyword_school != 0 & F1.base_contracts_keyword_school != . & F2.base_contracts_keyword_school != .
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & F1.base_contracts_keyword_school > 0 & base_contracts_keyword_school != 0 & F1.base_contracts_keyword_school != .
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & L1.base_contracts_keyword_school > 0 & base_contracts_keyword_school != 0 & L1.base_contracts_keyword_school != .
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & L1.base_contracts_keyword_school > 0 & L2.base_contracts_keyword_school > 0 & base_contracts_keyword_school != 0 & L1.base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria1_school = 1 if L1.base_contracts_keyword_school > 0 & L2.base_contracts_keyword_school > 0 & L1.base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & L2.base_contracts_keyword_school > 0 & base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria1_school = 1 if L1.base_contracts_keyword_school > 0 & F1.base_contracts_keyword_school > 0 & L1.base_contracts_keyword_school != 0 & F1.base_contracts_keyword_school != .
replace criteria1_school = 1 if base_contracts_keyword_school > 0 & F2.base_contracts_keyword_school > 0 & base_contracts_keyword_school != 0 & F2.base_contracts_keyword_school != .

gen criteria2_school = 0
replace criteria2_school = 1 if ((base_contracts_keyword_school+F1.base_contracts_keyword_school+F2.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & base_contracts_keyword_school != 0 & F1.base_contracts_keyword_school != . & F2.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+L1.base_contracts_keyword_school+L2.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & base_contracts_keyword_school != 0 & L1.base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+L1.base_contracts_keyword_school+F1.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & base_contracts_keyword_school != 0 & L1.base_contracts_keyword_school != . & F1.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((L1.base_contracts_keyword_school+L2.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & L1.base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+F1.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & F1.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+F2.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & F2.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+L1.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & L1.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((base_contracts_keyword_school+L2.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != . & L2.base_contracts_keyword_school != .
replace criteria2_school = 1 if ((F1.base_contracts_keyword_school+L1.base_contracts_keyword_school) > 4) & base_contracts_keyword_school != .  & L1.base_contracts_keyword_school != . & F1.base_contracts_keyword_school != .

gen base_school = 0
replace base_school = 1 if criteria1_school==1 & criteria2_school==1 
label var base_school "US military facility (Africa)"


restore


preserve
keep if psc_type=="E" | psc_type=="L" | psc_topcode=="V1" | psc_type=="W" | psc_type=="X" | psc_type=="Y" | psc_type=="Z" | psc_code=="8960"

collapse (sum) base_contracts_keyword=base_contract, by(pop sign)
rename pop country
rename sign year
replace country = "Yemen, Rep." if country=="Yemen"
replace country = "Venezuela, RB" if country=="Venezuela"
replace country = "Trinidad and Tobago" if country=="Trinidad And Tobago"
replace country = "Syrian Arab Republic" if country=="Syria"
replace country = "Slovak Republic" if country=="Slovakia"
replace country = "Russian Federation" if country=="Russia"
replace country = "Sao Tome and Principe" if country=="Sao Tome And Principe"
replace country = "Macedonia, FYR" if country=="Macedonia"
replace country = "Kyrgyz Republic" if country=="Kyrgyzstan"
replace country = "Lao PDR" if country=="Laos"
replace country = "Korea, Rep." if country=="Korea, South"
replace country = "Korea, Dem. Rep." if country=="Korea, North"
replace country = "Iran, Islamic Rep." if country=="Iran"
replace country = "Egypt, Arab Rep." if country=="Egypt"
replace country = "Cote d'Ivoire" if country=="Cote D'Ivoire"
replace country = "Congo, Rep." if country=="Congo (Brazzaville)"
replace country = "Congo, Dem. Rep." if country=="Congo (Kinshasa)"
replace country = "Cabo Verde" if country=="Cape Verde"
replace country = "Myanmar" if country=="Burma"
replace country = "Bosnia and Herzegovina" if country=="Bosnia And Herzegovina"
replace country = "Brunei Darussalam" if country=="Brunei"
replace country = "Antigua and Barbuda" if country=="Antigua And Barbuda"
rename base_contracts_keyword base_contracts_topcode_all

egen id = group(country)
xtset id year
tsfill, full
gen criteria1_topcode_all = 0
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & F1.base_contracts_topcode_all > 0 & F2.base_contracts_topcode_all > 0 & base_contracts_topcode_all != 0 & F1.base_contracts_topcode_all != . & F2.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & F1.base_contracts_topcode_all > 0 & base_contracts_topcode_all != 0 & F1.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & L1.base_contracts_topcode_all > 0 & base_contracts_topcode_all != 0 & L1.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & L1.base_contracts_topcode_all > 0 & L2.base_contracts_topcode_all > 0 & base_contracts_topcode_all != 0 & L1.base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if L1.base_contracts_topcode_all > 0 & L2.base_contracts_topcode_all > 0 & L1.base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & L2.base_contracts_topcode_all > 0 & base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if L1.base_contracts_topcode_all > 0 & F1.base_contracts_topcode_all > 0 & L1.base_contracts_topcode_all != 0 & F1.base_contracts_topcode_all != .
replace criteria1_topcode_all = 1 if base_contracts_topcode_all > 0 & F2.base_contracts_topcode_all > 0 & base_contracts_topcode_all != 0 & F2.base_contracts_topcode_all != .

gen criteria2_topcode_all = 0
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+F1.base_contracts_topcode_all+F2.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & base_contracts_topcode_all != 0 & F1.base_contracts_topcode_all != . & F2.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+L1.base_contracts_topcode_all+L2.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & base_contracts_topcode_all != 0 & L1.base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+L1.base_contracts_topcode_all+F1.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & base_contracts_topcode_all != 0 & L1.base_contracts_topcode_all != . & F1.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((L1.base_contracts_topcode_all+L2.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & L1.base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+F1.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & F1.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+F2.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & F2.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+L1.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & L1.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((base_contracts_topcode_all+L2.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != . & L2.base_contracts_topcode_all != .
replace criteria2_topcode_all = 1 if ((F1.base_contracts_topcode_all+L1.base_contracts_topcode_all) > 4) & base_contracts_topcode_all != .  & L1.base_contracts_topcode_all != . & F1.base_contracts_topcode_all != .

gen base_topcode_all = 0
replace base_topcode_all = 1 if criteria1_topcode_all==1 & criteria2_topcode_all==1
label var base_topcode_all "US military facility (Africa)"


restore
